﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using EIP.Common.Dapper.Extensions.Common;
using EIP.Common.Dapper.Extensions.DBUtility;
using EIP.Common.Entities.CustomAttributes;

namespace EIP.Common.Dapper.AdoNet
{
    public class AdoUtil
    {
        /// <summary>
        ///     通过提供的参数，执行无结果集的数据库操作命令
        ///     并返回执行数据库操作所影响的行数。
        /// </summary>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">
        ///     存储过程名称或者T-SQL命令行<</param>
        /// <param name="commandParameters">执行命令所需的参数数组</param>
        /// <returns>返回通过执行命令所影响的行数</returns>
        public static int ExecuteNonQuerys(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            var cmd = DbFactory.CreateDbCommand();

            using (var conn = DbFactory.CreateDbConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                var val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        ///     通过提供的参数，执行无结果集的数据库操作命令
        ///     并返回执行数据库操作所影响的行数。
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">
        ///     存储过程名称或者T-SQL命令行<</param>
        /// <returns>返回通过执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText)
        {
            var cmd = DbFactory.CreateDbCommand();

            using (var conn = DbFactory.CreateDbConnection(connectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
                var val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        ///     通过提供的参数，执行无结果集返回的数据库操作命令
        ///     并返回执行数据库操作所影响的行数。
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <param name="commandParameters">执行命令所需的参数数组</param>
        /// <returns>返回通过执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(IDbConnection connection, CommandType cmdType, string cmdText,
            params IDbDataParameter[] commandParameters)
        {
            var cmd = DbFactory.CreateDbCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            var val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     通过提供的参数，执行无结果集返回的数据库操作命令
        ///     并返回执行数据库操作所影响的行数。
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <returns>返回通过执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(IDbConnection connection, CommandType cmdType, string cmdText)
        {
            var cmd = DbFactory.CreateDbCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, null);
            var val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     使用SqlBulkCopy批量进行插入数据
        /// </summary>
        /// <typeparam name="T">实体对象</typeparam>
        /// <param name="entitys">实体对象集合</param>
        public static int InsertList<T>(List<T> entitys) where T : new()
        {
            int result = 1;
            try
            {
                using (var destinationConnection = new SqlConnection(ConnectionString))
                {
                    using (var bulkCopy = new SqlBulkCopy(destinationConnection))
                    {
                        Type type = entitys[0].GetType();
                        object classAttr = type.GetCustomAttributes(false)[0];
                        if (classAttr is TableAttribute)
                        {
                            TableAttribute tableAttr = classAttr as TableAttribute;
                            bulkCopy.DestinationTableName = tableAttr.Name; //要插入的表的表明 
                        }
                        ModelHandler<T> mh = new ModelHandler<T>();
                        DataTable dt = mh.FillDataTable(entitys);
                        try
                        {
                            destinationConnection.Open();
                            if (dt != null && dt.Rows.Count != 0)
                            {
                                bulkCopy.WriteToServer(dt);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw ex;
                        }
                    }
                }
            }
            catch (Exception)
            {
                result = 0;
                throw;
            }
            return result;
        }

        /// <summary>
        ///     通过提供的参数，执行无结果集返回的数据库操作命令
        ///     并返回执行数据库操作所影响的行数。
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="trans">sql事务对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">
        ///     存储过程名称或者T-SQL命令行</param>
        /// <param name="commandParameters">执行命令所需的参数数组</param>
        /// <returns>返回通过执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(IDbTransaction trans, CommandType cmdType, string cmdText,
            params IDbDataParameter[] commandParameters)
        {
            IDbConnection conn = null;
            if (trans == null)
            {
                conn = DbFactory.CreateDbConnection(ConnectionString);
            }
            else
            {
                conn = trans.Connection;
            }

            var cmd = DbFactory.CreateDbCommand();
            PrepareCommand(cmd, conn, trans, cmdType, cmdText, commandParameters);
            var val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     通过提供的参数，执行无结果集返回的数据库操作命令
        ///     并返回执行数据库操作所影响的行数。
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="trans">sql事务对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">
        ///     存储过程名称或者T-SQL命令行</param>
        /// <returns>返回通过执行命令所影响的行数</returns>
        public static int ExecuteNonQuery(IDbTransaction trans, CommandType cmdType, string cmdText)
        {
            var cmd = DbFactory.CreateDbCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, null);
            var val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     使用提供的参数，执行有结果集返回的数据库操作命令
        ///     并返回SqlDataReader对象
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     SqlDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new
        ///     SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">
        ///     存储过程名称或者T-SQL命令行</param>
        /// <param name="commandParameters">执行命令所需的参数数组</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static IDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
            params IDbDataParameter[] commandParameters)
        {
            var cmd = DbFactory.CreateDbCommand();
            var conn = DbFactory.CreateDbConnection(connectionString);

            //我们在这里使用一个 try/catch,因为如果PrepareCommand方法抛出一个异常，我们想在捕获代码里面关闭
            //connection连接对象，因为异常发生datareader将不会存在，所以commandBehaviour.CloseConnection
            //将不会执行。
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        ///     使用提供的参数，执行有结果集返回的数据库操作命令
        ///     并返回SqlDataReader对象
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">
        ///     存储过程名称或者T-SQL命令行<</param>
        /// <returns>返回SqlDataReader对象</returns>
        public static IDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText)
        {
            var cmd = DbFactory.CreateDbCommand();
            var conn = DbFactory.CreateDbConnection(connectionString);

            //我们在这里使用一个 try/catch,因为如果PrepareCommand方法抛出一个异常，我们想在捕获代码里面关闭
            //connection连接对象，因为异常发生datareader将不会存在，所以commandBehaviour.CloseConnection
            //将不会执行。
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
                var rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
                return rdr;
            }
            catch
            {
                conn.Close();
                throw;
            }
        }

        /// <summary>
        ///     查询数据填充到数据集DataSet中
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">命令文本</param>
        /// <param name="commandParameters">参数数组</param>
        /// <returns>数据集DataSet对象</returns>
        public static DataSet dataSet(string connectionString, CommandType cmdType, string cmdText,
            params IDbDataParameter[] commandParameters)
        {
            var ds = new DataSet();
            var cmd = DbFactory.CreateDbCommand();
            var conn = DbFactory.CreateDbConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                var sda = DbFactory.CreateDataAdapter(cmd);
                sda.Fill(ds);
                return ds;
            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     查询数据填充到数据集DataSet中
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">命令文本</param>
        /// <returns>数据集DataSet对象</returns>
        public static DataSet dataSet(string connectionString, CommandType cmdType, string cmdText)
        {
            var ds = new DataSet();
            var cmd = DbFactory.CreateDbCommand();
            var conn = DbFactory.CreateDbConnection(connectionString);
            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, null);
                var sda = DbFactory.CreateDataAdapter(cmd);
                sda.Fill(ds);
                return ds;
            }
            catch
            {
                conn.Close();
                throw;
            }
            finally
            {
                conn.Close();
                cmd.Dispose();
            }
        }

        /// <summary>
        ///     执行SQL语句返回DataTable对象
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="values">参数</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, SqlParameter[] values)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var dtTable = new DataTable();
                try
                {
                    connection.Open();
                    var sda = new SqlDataAdapter(sql, connection);
                    if (values != null)
                    {
                        sda.SelectCommand.Parameters.AddRange(values);
                    }
                    sda.Fill(dtTable);
                    return dtTable;
                }
                catch
                {
                    connection.Close();
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        ///     执行SQL语句返回DataTable对象
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            using (var connection = new SqlConnection(ConnectionString))
            {
                var ds = new DataSet();
                try
                {
                    connection.Open();
                    var cmd = new SqlCommand(sql, connection);
                    cmd.CommandTimeout = 360;
                    var sda = new SqlDataAdapter(cmd);
                    sda.Fill(ds);
                    return ds.Tables[0];
                }
                catch
                {
                    connection.Close();
                    throw;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }
        }

        /// <summary>
        ///     依靠数据库连接字符串connectionString,
        ///     使用所提供参数，执行返回首行首列命令
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <param name="commandParameters">执行命令所需的参数数组</param>
        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText,
            params IDbDataParameter[] commandParameters)
        {
            var cmd = DbFactory.CreateDbCommand();

            using (var connection = DbFactory.CreateDbConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                var val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        ///     依靠数据库连接字符串connectionString,
        ///     使用所提供参数，执行返回首行首列命令
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
        public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText)
        {
            var cmd = DbFactory.CreateDbCommand();

            using (var connection = DbFactory.CreateDbConnection(connectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, null);
                var val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        /// <summary>
        ///     依靠数据库连接字符串connectionString,
        ///     使用所提供参数，执行返回首行首列命令
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <param name="commandParameters">执行命令所需的参数数组</param>
        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
        public static object ExecuteScalar(IDbConnection connection, CommandType cmdType, string cmdText,
            params IDbDataParameter[] commandParameters)
        {
            var cmd = DbFactory.CreateDbCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            var val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     依靠数据库连接字符串connectionString,
        ///     使用所提供参数，执行返回首行首列命令
        /// </summary>
        /// <remarks>
        ///     e.g.:
        ///     Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid",
        ///     24));
        /// </remarks>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行</param>
        /// <returns>返回一个对象，使用Convert.To{Type}将该对象转换成想要的数据类型。</returns>
        public static object ExecuteScalar(IDbConnection connection, CommandType cmdType, string cmdText)
        {
            var cmd = DbFactory.CreateDbCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, null);
            var val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            return val;
        }

        /// <summary>
        ///     add parameter array to the cache
        /// </summary>
        /// <param name="cacheKey">Key to the parameter cache</param>
        /// <param name="commandParameters">an array of SqlParamters to be cached</param>
        public static void CacheParameters(string cacheKey, params IDbDataParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }

        /// <summary>
        ///     查询缓存参数
        /// </summary>
        /// <param name="cacheKey">使用缓存名称查找值</param>
        /// <returns>缓存参数数组</returns>
        public static IDbDataParameter[] GetCachedParameters(string cacheKey)
        {
            var cachedParms = (IDbDataParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            var clonedParms = new IDbDataParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (IDbDataParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }

        /// <summary>
        ///     为即将执行准备一个命令
        /// </summary>
        /// <param name="cmd">SqlCommand对象</param>
        /// <param name="conn">SqlConnection对象</param>
        /// <param name="trans">IDbTransaction对象</param>
        /// <param name="cmdType">执行命令的类型（存储过程或T-SQL，等等）</param>
        /// <param name="cmdText">存储过程名称或者T-SQL命令行, e.g. Select * from Products</param>
        /// <param name="cmdParms">SqlParameters to use in the command</param>
        private static void PrepareCommand(IDbCommand cmd, IDbConnection conn, IDbTransaction trans, CommandType cmdType,
            string cmdText, IDbDataParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (var parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        /// <summary>
        ///     根据传入的Key获取配置文件中
        ///     相应Key的数据库连接字符串
        /// </summary>
        /// <param name="Key"></param>
        /// <returns></returns>
        public static string GetConnectionString(string Key)
        {
            try
            {
                return CommonUtils.GetConfigValueByKey(Key);
            }
            catch
            {
                throw new Exception("web.config文件appSettings中数据库连接字符串未配置或配置错误，必须为Key=\"connectionString\"");
            }
        }

        /// <summary>
        ///     用于数据库类型的字符串枚举转换
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="value"></param>
        /// <returns></returns>
        public static T DatabaseTypeEnumParse<T>(string value)
        {
            try
            {
                return CommonUtils.EnumParse<T>(value);
            }
            catch
            {
                throw new Exception("数据库类型\"" + value + "\"错误，请检查！");
            }
        }

        /// <summary>
        ///     构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>返回command对象</returns>
        private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName,
            IDataParameter[] parameters)
        {
            var command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        #region 关闭数据库连接

        /// <summary>
        ///     关闭数据库连接
        /// </summary>
        /// <param name="con">当前数据库连接对象</param>
        public void Close(SqlConnection con)
        {
            //判断连接是否已经创建
            if (con != null)
            {
                //判断连接的状态是否打开
                if (con.State == ConnectionState.Open)
                {
                    try
                    {
                        con.Close();
                        con.Dispose();
                    }
                    catch (SqlException E)
                    {
                        throw new Exception("关闭数据库操作失败!" + E.Message);
                    }
                }
            }
        }

        #endregion

        /// <summary>
        ///     执行SQL语句，返回影响的记录数
        /// </summary>
        /// <param name="SQLString">SQL语句</param>
        /// <param name="cmdParms">参数数组</param>
        /// <returns>返回受影响的函数，大于零成功，否则失败</returns>
        public int ExecuteSql(string SQLString, params SqlParameter[] cmdParms)
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand())
                {
                    try
                    {
                        if (con.State != ConnectionState.Open)
                            con.Open();
                        PrepareCommand(cmd, con, null, SQLString, cmdParms);
                        var rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();

                        //记录db操作
                        return rows;
                    }
                    catch (SqlException)
                    {
                        return 0;
                    }
                    finally
                    {
                        Close(con);
                    }
                }
            }
        }

        /// <summary>
        /// 返回指定表内指定条件的行数
        /// </summary>
        /// <param name="filedName"></param>
        /// <param name="pras"></param>
        /// <returns></returns>
        public int GetTableRow(string filedName, SqlParameter[] pras)
        {
            SqlConnection cnnstr = new SqlConnection(ConnectionString);
            var ret = 0;
            cnnstr.Open();
            var sqltrancmd = cnnstr.BeginTransaction();
            var cmd = new SqlCommand();
            cmd.Transaction = sqltrancmd;
            try
            {
                cmd.Connection = cnnstr;
                cmd.CommandText = filedName;
                if (pras != null)
                    cmd.Parameters.AddRange(pras);
                ret = Convert.ToInt32(cmd.ExecuteScalar());
                sqltrancmd.Commit();
                cmd.Parameters.Clear();
                //记录db操作
                return ret;
            }
            catch (Exception ex)
            {
                sqltrancmd.Rollback();
                return ret;
            }
            finally
            {
                cmd.Dispose();
                cnnstr.Close();
            }
        }

        /// <summary>
        ///     为执行command添加参数列表
        /// </summary>
        /// <param name="cmd">command对象</param>
        /// <param name="conn">数据连接对象</param>
        /// <param name="trans">事物控制对象</param>
        /// <param name="cmdText">sql语句</param>
        /// <param name="cmdParms">执行的参数</param>
        private void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, string cmdText,
            SqlParameter[] cmdParms)
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                if (con.State != ConnectionState.Open)
                    con.Open();
                cmd.Connection = conn;
                cmd.CommandText = cmdText;
                if (trans != null)
                    cmd.Transaction = trans;
                cmd.CommandType = CommandType.Text; //cmdType;
                if (cmdParms != null)
                {
                    foreach (var parm in cmdParms)
                        cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        ///     执行存储过程返回dataset
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="commandParameters">存储过程参数</param>
        /// <returns>DataSet 已经经过处理，没有数据将返回null</returns>
        public virtual DataSet ExecuteDataset(CommandType commandType, string storedProcName,
            params SqlParameter[] commandParameters)
        {
            using (var con = new SqlConnection(ConnectionString))
            {
                var dataSet = new DataSet();
                try
                {
                    if (con.State != ConnectionState.Open)
                        con.Open();

                    var sqlDA = new SqlDataAdapter();
                    sqlDA.SelectCommand = BuildQueryCommand(con, storedProcName, commandParameters);
                    sqlDA.Fill(dataSet);

                    //记录db操作
                    if (dataSet != null && dataSet.Tables[0].Rows.Count > 0)
                        return dataSet;
                    return null;
                }
                catch (SqlException E)
                {
                    throw new Exception("执行存储过程返回dataset操作失败!" + E.Message);
                }
                finally
                {
                    Close(con);
                }
            }
        }

        /// <summary>
        ///     执行存储过程，返回受影响的行数
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="commandParameters">存储过程参数</param>
        /// <returns>返回受影响的行数，大于零为成功，否则失败</returns>
        public virtual int ExecuteNonQuery(CommandType commandType, string storedProcName,
            params SqlParameter[] commandParameters)
        {
            var rowsAffected = 0;
            using (var con = new SqlConnection(ConnectionString))
            {
                try
                {
                    if (con.State != ConnectionState.Open)
                        con.Open();
                    var command = BuildQueryCommand(con, storedProcName, commandParameters);
                    rowsAffected = command.ExecuteNonQuery();
                    //result = (int)command.Parameters["ReturnValue"].Value;
                    //记录db操作

                    return rowsAffected;
                }
                catch (SqlException E)
                {
                    throw new Exception("执行存储过程,返回受影响的行数失败!" + E.Message);
                    return rowsAffected;
                }
                finally
                {
                    Close(con);
                }
            }
        }

        /// <summary>
        ///     执行存储过程返回dataset
        /// </summary>
        /// <param name="sp">存储过程中所用参数实体</param>
        public DataSet SPExecuteDataSet(StoredProcedure sp)
        {
            return ExecuteDataset(CommandType.StoredProcedure, sp.SPName, sp.SPParams.ToArray());
        }

        /// <summary>
        ///     执行存储过程返回影响行数
        /// </summary>
        /// <param name="sp">存储过程中所用参数实体</param>
        public int SPExecuteNonQuery(StoredProcedure sp)
        {
            return ExecuteNonQuery(CommandType.StoredProcedure, sp.SPName, sp.SPParams.ToArray());
        }

        //获取数据库类型
        private static readonly string strDbType = CommonUtils.GetConfigValueByKey("dbType");
        //将数据库类型转换成枚举类型
        public static DatabaseType DbType = DatabaseTypeEnumParse<DatabaseType>(strDbType);
        //获取数据库连接字符串
        public static string ConnectionString = ConfigurationManager.AppSettings["connectionStrings"];
        //获取数据库命名参数符号，比如@(SQLSERVER)、:(ORACLE)
        public static string DbParmChar = DbFactory.CreateDbParmCharacter();
        private static readonly Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
    }
}